package com.jixiantubu.entity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.gemptc.bean.ReplyBean;
import com.gemptc.bean.ReplyInfoBean;
import com.gemptc.utils.DBUtil;
import com.jixiantubu.factory.Reply_InfoDao;

public class Reply_InfoDaoimp implements Reply_InfoDao {

	@Override
	public boolean insert(int topicId, String username, String replyname,
			String content, String createTime, int rank) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		boolean falg = false;
		int userid = Integer.parseInt(username);
		int replyId = Integer.parseInt(replyname);
		String sql = "insert into reply_post (pi_TopicId,user_id,reply_user_id,rp_content,rp_createTime,rp_rank)"
				+ " values (?,?,?,?,?,?)";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, topicId);
			preparedStatement.setInt(2, replyId);
			preparedStatement.setInt(3, userid);
			preparedStatement.setString(4, content);
			preparedStatement.setString(5, createTime);
			preparedStatement.setInt(6, rank);
			System.out.println(topicId + "--" + userid + "---" + replyId
					+ content + createTime + rank);
			preparedStatement.execute();
			falg = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return falg;
	}

	@Override
	public List<ReplyInfoBean> selectReply( int topicID) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<ReplyInfoBean> list = new ArrayList<ReplyInfoBean>();
		
		String sql = "SELECT reply_post.* FROM reply_post,post_info WHERE reply_post.`user_id`=post_info.`user_id` AND reply_post.`pi_TopicID`=post_info.`pi_TopicID` AND reply_post.`pi_TopicID`= ? AND reply_post.`rp_rank` != 0;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, topicID);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int rp_id = resultSet.getInt(1);
				int pi_TopicID = resultSet.getInt(2);
				int userid = resultSet.getInt(4);
				int reply_id = resultSet.getInt(4);
				String content = resultSet.getString(5);
				String createTime = resultSet.getString(7);
				int rank = resultSet.getInt(8);
				String username = select_usernickname(userid);
				String replynickname =select_usernickname(reply_id);
				int phid = selectPhid1(topicID, reply_id);
				String replyname_imgpath=selectuserimg(reply_id);
				String phimg = selectPhoto(reply_id, rp_id, phid);
				System.out.println("reply_id" + reply_id + "rp_id" + rp_id
						+ "phid" + phid);
				ReplyInfoBean replyBean = new ReplyInfoBean(rp_id, userid,
						reply_id, pi_TopicID, username, replynickname,
						replyname_imgpath, rank, createTime, content, phimg);
				list.add(replyBean);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return list;
	}

	private String selectuserimg(int reply_id) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String img = null;
		String sql = "SELECT user_imgPath FROM users WHERE  user_id=? LIMIT 1;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, reply_id);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				img = resultSet.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return img ;
	}

	private String select_usernickname(int id) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String username = null;
		String sql = "SELECT user_nickname FROM users WHERE  user_id=? LIMIT 1;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				username = resultSet.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return username;
	}

	@Override
	public List<ReplyInfoBean> selectReply_Reply( int topicid, int rpid) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		ReplyInfoBean replyInfoBean;
		List<ReplyInfoBean> list = new ArrayList<ReplyInfoBean>();
		String sql = "SELECT * FROM reply_post WHERE pi_TopicID= ? AND rp_id= ? AND rp_rank= 0;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, topicid);
			preparedStatement.setInt(2, rpid);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int rp_id = resultSet.getInt(1);
				int topic_id = resultSet.getInt(2);
				int user_id = resultSet.getInt(3);
				int replyid = resultSet.getInt(4);
				String content = resultSet.getString(5);
				String createTime = resultSet.getString(7);
							
				String username = select_usernickname(user_id);
				String replyname = select_usernickname(replyid);
				
				replyInfoBean = new ReplyInfoBean(rp_id, user_id, replyid,
						topic_id, username, replyname, createTime, content);
				
				list.add(replyInfoBean);
				System.out.println("reply_reply.list>>>"+list.toString());

			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return list;
	}

	private String selectPhoto(int userid, int rpid, int phid) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String pl_path = null;
		String sql = "SELECT pl_path FROM photo_library WHERE user_id=? AND rp_id=? AND ph_id=?;";

		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, userid);
			preparedStatement.setInt(2, rpid);
			preparedStatement.setInt(3, phid);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				pl_path = resultSet.getString(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return pl_path;
	}

	@Override
	public List<ReplyInfoBean> selectByTime(int userid, int topicID) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<ReplyInfoBean> list = new ArrayList<ReplyInfoBean>();
		// List<String> list2=new ArrayList<String>();
		String username = select_usernickname(userid);
		String sql = "SELECT reply_post.`rp_id`,reply_post.`pi_TopicID`,reply_post.`user_id`,reply_post.`reply_user_id`,reply_post.`rp_content`,reply_post.`rp_createTime`,reply_post.`rp_rank`,users.`user_nickname`,users.`user_imgPath` FROM users,reply_post "
				+ "WHERE users.`user_id` = reply_post.`reply_user_id` AND reply_post.`user_id` = ? AND reply_post.`pi_TopicID` = ?  order by reply_post.`rp_id` desc;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, userid);
			preparedStatement.setInt(2, topicID);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int rp_id = resultSet.getInt(1);
				int pi_TopicID = resultSet.getInt(2);
				int reply_id = resultSet.getInt(4);
				String content = resultSet.getString(5);
				String createTime = resultSet.getString(6);
				int rank = resultSet.getInt(7);
				String replynicname = resultSet.getString(8);
				String replyname_imgpath = resultSet.getString(9);
				int phid = selectPhid1(pi_TopicID, reply_id);
				String phimg = selectPhoto(userid, rp_id, phid);
				ReplyInfoBean replyInfoBean = new ReplyInfoBean(rp_id, userid,
						reply_id, pi_TopicID, username, replynicname,
						replyname_imgpath, rank, createTime, content, phimg);
				list.add(replyInfoBean);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return list;
	}

	private int selectPhid1(int topicid, int userid) {
		int id = 0;
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = "select ph_id from photo where pi_TopicID=? and user_id=?";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, topicid);
			preparedStatement.setInt(2, userid);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				id = resultSet.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return id;
	}

	@Override
	public boolean insertReply(int rpid, int topicId, int userid,
			int replyId, String content, String createTime, int rank) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		boolean falg = false;
		String sql = "insert into reply_post (rp_id,pi_TopicId,user_id,reply_user_id,rp_content,rp_createTime,rp_rank)"
				+ " values (?,?,?,?,?,?,?)";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, rpid);
			preparedStatement.setInt(2, topicId);
			preparedStatement.setInt(3, replyId);
			preparedStatement.setInt(4, userid);
			preparedStatement.setString(5, content);
			preparedStatement.setString(6, createTime);
			preparedStatement.setInt(7, rank);
			System.out.println(topicId + "--" + userid + "---" + replyId
					+ content + createTime + rpid);
			preparedStatement.execute();
			falg = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return falg;
	}

	@Override
	public List<ReplyInfoBean> selectLouzhu(int topic) {
		List<ReplyInfoBean> list = new ArrayList<ReplyInfoBean>();
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = "SELECT reply_post.`rp_id`,reply_post.`pi_TopicID`,"
				+ "reply_post.`user_id`,reply_post.`reply_user_id`,reply_post.`rp_content`,"
				+ "reply_post.`rp_createTime`,reply_post.`rp_rank`,users.`user_nickname`,"
				+ "users.`user_imgPath` FROM users,reply_post "
				+ "WHERE reply_post.`pi_TopicID` = ? and reply_post.`user_id`=reply_post.`reply_user_id` AND reply_post.`user_id`=users.`user_id` AND rp_rank !=0  ;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, topic);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int rp_id = resultSet.getInt(1);
				int pi_TopicID = resultSet.getInt(2);
				int reply_id = resultSet.getInt(4);
				String content = resultSet.getString(5);
				String createTime = resultSet.getString(6);
				int rank = resultSet.getInt(7);
				String replynicname = resultSet.getString(8);
				String replyname_imgpath = resultSet.getString(9);
				int phid = selectPhid1(pi_TopicID, reply_id);
				String phimg = selectPhoto(reply_id, rp_id, phid);
				System.out.println("reply_id" + reply_id + "rp_id" + rp_id
						+ "phid" + phid);
				ReplyInfoBean replyBean = new ReplyInfoBean(rp_id, reply_id,
						reply_id, pi_TopicID, replynicname, replynicname,
						replyname_imgpath, rank, createTime, content, phimg);
				list.add(replyBean);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return list;
	}

	public boolean insertCollect(int uid, int topic) {
		boolean falg = false;
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = "INSERT INTO collect (user_id,pi_TopicID) VALUES (?,?);";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, uid);
			preparedStatement.setInt(2, topic);
			preparedStatement.execute();
			falg = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}

		return falg;
	}

	public boolean selectCollect(int uid, int topic) {
		boolean falg = false;
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = "SELECT * FROM collect WHERE user_id= ? AND pi_TopicID= ? ;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, uid);
			preparedStatement.setInt(2, topic);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				falg = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return falg;
	}
	
	public List<ReplyInfoBean> selectReply_Reply3( int topicID) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<ReplyInfoBean> list = new ArrayList<ReplyInfoBean>();
		
		String sql = "SELECT * FROM reply_post WHERE reply_post.`pi_TopicID`= ?;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, topicID);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int rp_id = resultSet.getInt(1);
				int pi_TopicID = resultSet.getInt(2);
				int userid = resultSet.getInt(3);
				int reply_id = resultSet.getInt(4);
				String content = resultSet.getString(5);
				String createTime = resultSet.getString(7);
				int rank = resultSet.getInt(8);
				String username = select_usernickname(userid);
				String replynickname =select_usernickname(reply_id);
				int phid = selectPhid1(topicID, reply_id);
				String replyname_imgpath=selectuserimg(reply_id);
				String phimg = selectPhoto(reply_id, rp_id, phid);
				ReplyInfoBean replyBean = new ReplyInfoBean(rp_id, userid,
						reply_id, pi_TopicID, username, replynickname,
						replyname_imgpath, rank, createTime, content, phimg);
				list.add(replyBean);
				System.out.println(list.toString());
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return list;
	}
	//回复次数
	public boolean updateScan(int topic,int scan){
		boolean falg=false;
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql="UPDATE  post_info SET pi_reply=? WHERE pi_TopicID=?;";
		try {
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setInt(1, scan);
			preparedStatement.setInt(2, topic);
			preparedStatement.execute();
			falg=true;
		} catch (Exception e) {
			e.printStackTrace();
		}	finally{
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return falg;
		
	}
	
}
